<?php
// +----------------------------------------------------------------------
// | SwiftPHP [ JUST DO ONE THING WELL ]
// +----------------------------------------------------------------------
// | Copyright (c) 2015-2017  http://swiftphp.zhongwenyu.com All rights reserved.
// +----------------------------------------------------------------------
// | Author: zhongwenyu <zhongwenyu1987@163.com> <http://www.zhongwenyu.com>
// +----------------------------------------------------------------------
// | Times: 2017/2/24 15:11
// +----------------------------------------------------------------------
namespace swift\db;
use swift\Db;
use swift\db\Connection;

class Query{
    // sql语句查询参数
    protected $options = [
        'table' => '',   // 完整表名
        'where' => '',   // where条件
        'field' => '',
        'order' => '',
        'limit' => [],
        'page' => '',
        'group' => '',
        'having' => '',
        'join' => '',
        'alias' => [],   // 表别名，用于join查询
        'merge' => ''
    ];
    protected $bind = [];    // 参数绑定
    protected $connection;    // 数据库Connection对象实例
    protected $prefix = '';    // 当前数据表前缀
    protected $model;    // 模型名
    public $fetchSql = false;    // 是否返回sql语句
    protected $primary_key;   // 表主键
    protected $fields;   // 表字段信息
    protected $tableInfo;    // 表信息

    /**
     * 架构函数
     * @access public
     * @param Connection $connection 数据库对象实例
     * @param string     $model      模型名
     */
    public function __construct(Connection $connection = null , $model = ''){
        $this->connection = is_null($connection) ? Db::connect([] , true) : $connection;
        $this->prefix = $this->connection->getConfig('prefix');
        $this->model = $model;
    }

    /**
     * 设置表信息
     * @param string $table
     */
    protected function setTableInfo($table = ''){
        $table = empty($table) ? $this->options['table'] : $table;
        if(!empty($table)){
            $fields = $this->connection->getFields($this->options['table']);
            $this->fields = array_keys($fields);
            foreach($fields as $k => $v){
                if($v['primary'] === true){
                    $this->primary_key = $k;
                    return;
                }
            }
        }
    }

    /**
     * 返回表主键
     * @param string $table
     * @return $string $primary_key
     */
    public function getPk($table = ''){
        $table = empty($table) ? $this->options['table'] : $table;
        $fields = $this->connection->getFields($table);
        foreach($fields as $k => $v){
            if($v['primary'] === true){
                $primary_key = $k;
            }
        }
        return $primary_key;
    }

    /**
     * 返回表字段信息
     * @param string $table
     * @return $array $fields
     */
    public function getFields($table = ''){
        $table = empty($table) ? $this->options['table'] : $table;
        $fields = $this->connection->getFields($table);
        return array_keys($fields);
    }

    // 设置当前表名
    public function setTable($table = ''){
        if(!empty($table)){
            $this->options['table'] = (!empty($this->prefix) && 0 === strpos($table , $this->prefix)) ? $table : $this->prefix . $table;
        }
    }

    // 获取表字段信息信息
    public function getFieldsInfo($table){
        $table = (!empty($this->prefix) && 0 === strpos($table , $this->prefix)) ? $table : $this->prefix . $table;
        return $this->connection->getFields($table);
    }

    // 返回wher条件
    public function getWhere(){
        return $this->options['where'];
    }

    // 设置where条件
    public function setWhere($where){
        $this->options['where'] = $where;
        return $this;
    }

    /**
     * where查询条件
     * @access public
     * @param mixed $field 字段名
     * @param mixed $condition 查询表达式
     * @param mixed $value 字段值
     * @param mixed $type 多个查询条件连接条件
     * @return $this
     */
    public function where($field , $condition = false , $value = '' , $type = '' , $bind = true){
        if(is_array($field)){
            if(is_array(current($field))){
                // 多个查询条件
                if($condition === false){
                    foreach($field as $v){
                        list($a,$b,$c,$d,$e) = $v;
                        if(is_string($a) && !empty($this->options['where']) && is_null($d)){
                            $d = 'and';
                        }
                        $this->where($a,$b,$c,$d,$e);
                    }
                }else{
                    $where = $this->whereChild($field , $condition);
                    $this->options['where'] .= ltrim($where);
                }
            }else{
                list($field,$condition,$value,$type,$bind) = $field;
                $this->where($field,$condition,$value,$type,$bind);
            }
        }else{
            if(is_string($field) && $condition === false){
                $this->options['where'] .= $field . ' ';
            }else{
                $this->options['where'] .= $this->whereCreate($field,$condition,$value,$type,$bind);
            }
        }
        return $this;
    }

    private function whereChild($condition , $type = 'or' , $flag = true){
        if($flag === true && empty($this->options['where'])){
            $flag = false;
        }else{
            $flag = true;
        }
        $where = $flag ? ' ' . strtoupper($type) . " (" : '';
        if(is_array($condition)){
            foreach($condition as $v){
                if(is_array($v[0])){
                    $where .= $this->whereChild($v[0] , $v[1] , true);
                }else{
                    list($a,$b,$c,$d,$e) = $v;
                    $where .= ' ' . $this->whereCreate($a,$b,$c,$d,$e,false);
                }
            }
        }
        $where .= $flag ? ') ' : ' ';
        return $where;
    }

    private function whereCreate($field , $condition = '' , $value = '' , $type = '' , $bind = true , $auto_type = true){
        is_null($bind) && $bind = true;
        // 单个查询条件
        // 查询语法 = , < , > , <= , >= , <> , like , between , not between , null , not null , in , not in
        $condition = strtoupper(trim($condition));
        $ca = (strpos($field,'.') === false && strpos($field,'(') === false) ? "`" : '';
        if(empty($this->options['where']) && $auto_type){
            $where = $ca . $field . $ca . ' ';
        }else{
            $where = (empty($type) ? '' : strtoupper($type) . ' '). $ca . $field . $ca . ' ';
        }
        // 查询表达式匹配
        switch(true){
            case in_array($condition , ['=','<','>','<=','>=','<>']) :
                $where .= $this->whereCommon($field , $value , $condition , $bind);
                break;
            case 'LIKE' == $condition :
                $where .= $this->whereLike($field , $value , $condition);
                break;
            case in_array($condition , ['BETWEEN','NOT BETWEEN']) && is_array($value) :
                $where .= $this->whereBetween($field , $value , $condition);
                break;
            case in_array($condition , ['IN','NOT IN']) && is_array($value) :
                $where .= $this->whereIn($field , $value , $condition);
                break;
            case in_array($condition , ['NULL','NOT NULL']) :
                $where .= 'IS ' . $condition . ' ';
                break;
            default : return false;
        }
        return $where;
    }

    // in查询表达式
    private function whereIn($field , $value , $condition){
        $vals = [];
        foreach($value as $k => $v){
            $key = $this->whereBindKey($field);
            $this->bind[$key] = $v;
            $vals[] = ':' . $key;
        }
        return $condition . ' ' . '(' . implode(',',$vals) . ')' . ' ';
    }

    // Between查询表达式
    private function whereBetween($field , $value , $condition){
        $key1 = $this->whereBindKey($field);
        $this->bind[$key1] = $value[0];
        $key2 = $this->whereBindKey($field);
        $this->bind[$key2] = $value[1];
        return $condition . ' ' . ':' . $key1 . ' AND '. ':' . $key2 . ' ';
    }

    // Like查询表达式
    private function whereLike($field , $value , $condition){
        $key = $this->whereBindKey($field);
        $this->bind[$key] = '%'.$value.'%';
        return $condition . " :" . $key . " ";
    }

    // 通用查询表达式
    private function whereCommon($field , $value , $condition , $bind = true){
        if($bind){
            $key = $this->whereBindKey($field);
            $this->bind[$key] = $value;
            $return = $condition . ' ' . ':' . $key . ' ';
        }else{
            $return = $condition . ' ' . $value . ' ';
        }
        return $return;
    }

    // 参数绑定中同名键的处理
    private function whereBindKey($field){
        strpos($field , '.') !== false && $field = str_replace('.','_',$field);
        return isset($this->bind[$field]) ? $field . count($this->bind) : $field;
    }

    /**
     * 指定当前操作的完整表名
     * @access public
     * @param mixed $table 表名
     * @return $this
     */
    public function table($table = ''){
        if(!empty($table) && is_string($table)){
            $this->options['table'] = $table;
            return $this;
        }else{
            throw new \Exception("表名不能为空！");
        }
    }

    /**
     * 指定当前操作的不含表前缀的表名
     * @access public
     * @param mixed $name表名
     * @return $this
     */
    public function name($name){
        if(!empty($name) && is_string($name)){
            if(strpos($name , 'as') !== false){
                $arr = explode('as',$name);
                $name = trim($arr[0]);
                $this->alias($name , trim($arr[1]));
            }

            if(strpos($name , $this->prefix) === false){
                $this->options['table'] = $this->prefix . $name;
            }else{
                $this->options['table'] = $name;
            }
            return $this;
        }else{
            throw new \Exception("表名不能为空！");
        }
    }

    /**
     * 查找单条记录
     * @access public
     * @param array|string|Query|\Closure $data
     * @return array|false|\PDOStatement|string|Model
     * @throws DbException
     * @throws ModelNotFoundException
     * @throws DataNotFoundException
     */
    public function find(){
        $this->limit(1);
        $sql = $this->selectSql();
        // 返回真实sql语句
        if($this->fetchSql){
            $realSql = $this->connection->getRealSql($sql , $this->bind);
            // 重置sql信息
            $this->resetSql();
            return $realSql;
        }

        // 返回查询结果
        $result = $this->connection->query($sql, $this->bind);

        // 重置sql信息
        $this->resetSql();

        if(is_array($result) && isset($result[0])){
            return $result[0];
        }else{
            return [];
        }
    }

    /**
     * 查找单个字段
     * @param string $field
     * @return bool|string
     */
    public function getField($field = ''){
        empty($field) && $field = trim($this->options['field']);

        if(empty($field) || strpos($field,',') !== false){
            return false;
        }

        $this->limit(1);
        $sql = $this->selectSql();
        // 返回真实sql语句
        if($this->fetchSql){
            $realSql = $this->connection->getRealSql($sql , $this->bind);
            // 重置sql信息
            $this->resetSql();
            return $realSql;
        }

        // 返回查询结果
        $result = $this->connection->query($sql, $this->bind);

        // 重置sql信息
        $this->resetSql();

        $field = strtolower($this->options['field']);
        if(strpos($field , '.') !== false){
            $field = substr(trim(strstr($field , '.')),1);
        }
        if(strpos($field , 'as') !== false){
            $key = substr(trim(strstr($field , 'as')),2);
        }else{
            $key = trim($field);
        }

        if(is_array($result) && isset($result[0][$field])){
            return $result[0][$key];
        }else{
            return '';
        }
    }

    /**
     * 获取最后一次执行的sql
     */
    public function getLastSql(){
        return $this->connection->getLastSql();
    }

    /**
     * 查找多条记录
     * @access public
     * @param array|string|Query|\Closure $data
     * @return array|false|\PDOStatement|string|Model
     * @throws DbException
     * @throws ModelNotFoundException
     * @throws DataNotFoundException
     */
    public function select(){
        $sql = $this->selectSql();
        // 返回真实sql语句
        if($this->fetchSql){
            $realSql = $this->connection->getRealSql($sql , $this->bind);
            // 重置sql信息
            $this->resetSql();
            return $realSql;
        }
        // 返回查询结果
        $result = $this->connection->query($sql, $this->bind);

        // 重置sql信息
        $this->resetSql();

        if(is_array($result) && !empty($result)){
            return $result;
        }else{
            return [];
        }
    }

    /**
     * 返回指定字段值组成的一维数组
     * @param $field
     * @return array|bool|string
     */
    public function getCol($field){
        if(!empty($field)){
            $this->options['field'] = $field;
        }else{
            $field = strtolower($this->options['field']);
        }
        if(empty($field) || (strpos($field , ',') !== false)){
            return false;
        }

        $sql = $this->selectSql();
        // 返回真实sql语句
        if($this->fetchSql){
            $realSql = $this->connection->getRealSql($sql , $this->bind);
            // 重置sql信息
            $this->resetSql();
            return $realSql;
        }
        // 返回查询结果
        $result = $this->connection->query($sql, $this->bind);

        $field = strtolower($this->options['field']);
        if(strpos($field , '.') !== false){
            $field = substr(strstr($field , '.'),1);
        }
        if(strpos($field , 'as') !== false){
            $field = substr(trim(strstr($field , 'as')),2);
        }

        $key = trim($field);
        $arr = [];
        foreach($result as $k => $v){
            $arr[$k] = $v[$key];
        }

        // 重置sql信息
        $this->resetSql();

        if(!empty($arr)){
            return $arr;
        }else{
            return [];
        }
    }

    // 聚合查询
    private function mergeSelect($sql , $alias){
        // 返回真实sql语句
        if($this->fetchSql){
            $realSql = $this->connection->getRealSql($sql , $this->bind);
            // 重置sql信息
            $this->resetSql();
            return $realSql;
        }
        // 返回查询结果
        $result = $this->connection->query($sql, $this->bind);

        // 重置sql信息
        $this->resetSql();

        return $result[0][$alias];
    }

    /**
     * 添加列
     * @access public
     * @param mixed $column    列名
     * @param mixed $type      类型
     * @param mixed $default   默认值
     * @param mixed $null      是否允许空值
     * @return bool
     */
    public function addColumn($column , $type = '' , $default = null , $null = false){
        if($null === false){
            // 不允许空值
            if(false !== strpos($type , 'int')){
                // 默认0
                $default = 0;
            }else{
                // 默认空字符串
                $default = '';
            }
        }
        // 拼接sql语句
        $sql = "alter table " . $this->options['table'] . " add column " . $column;
        !empty($type) && $sql .= ' ' . $type;
        if(!is_null($default)){
            if(is_numeric($default)){
                $sql .= " default " . $default;
            }else{
                $sql .= " default '" . $default . "'";
            }
        }
        if($null){
            $sql .= ' null';
        }else{
            $sql .= ' not null';
        }
        // 返回真实sql语句
        if($this->fetchSql){
            $realSql = $this->connection->getRealSql($sql , $this->bind);
            // 重置sql信息
            $this->resetSql();
            return $realSql;
        }
        return $this->connection->query($sql);
    }

    /**
     * COUNT查询
     * @access public
     * @return integer|string
     */
    public function count(){
        $this->options['merge'] = 'COUNT(*) AS sw_count';
        $sql = $this->selectSql();
        return $this->mergeSelect($sql , 'sw_count');
    }

    /**
     * SUM查询
     * @access public
     * @param string $field 字段名
     * @return integer|string
     */
    public function sum($field){
        $this->options['merge'] = 'SUM(`'. $field .'`) AS sw_sum';
        $sql = $this->selectSql();
        return $this->mergeSelect($sql , 'sw_sum');
    }

    /**
     * MAX查询
     * @access public
     * @param string $field 字段名
     * @return integer|string
     */
    public function max($field){
        $this->options['merge'] = 'MAX(`'. $field .'`) AS sw_max';
        $sql = $this->selectSql();
        return $this->mergeSelect($sql , 'sw_max');
    }

    /**
     * MIN查询
     * @access public
     * @param string $field 字段名
     * @return integer|string
     */
    public function min($field){
        $this->options['merge'] = 'MIN(`'. $field .'`) AS sw_min';
        $sql = $this->selectSql();
        return $this->mergeSelect($sql , 'sw_min');
    }

    /**
     * AVG查询
     * @access public
     * @param string $field 字段名
     * @return integer|string
     */
    public function avg($field){
        $this->options['merge'] = 'AVG(`'. $field .'`) AS sw_avg';
        $sql = $this->selectSql();
        return $this->mergeSelect($sql , 'sw_avg');
    }

    /**
     * 生成查询SQL
     * @access public
     * @return string
     */
    protected function selectSql(){
        $sql = 'SELECT ';
        // 拼接查询字段
        if(empty($this->options['merge'])){
            $sql .= (!empty($this->options['field']) ? $this->options['field'] . ' ' : '* ');
        }else{
            $sql .= $this->options['merge'] . ' ';
        }
        // 拼接表名
        $alias = $this->options['alias'][$this->options['table']];
        $table = !empty($alias) ? $this->options['table'] . ' AS ' . $alias : $this->options['table'];
        $sql = $sql . 'FROM ' . $table . ' ';
        // 拼接join连接
        !empty($this->options['join']) && $sql = $sql . $this->options['join'] . ' ';
        // 拼接where
        !empty($this->options['where']) && $sql = $sql . 'WHERE ' . $this->options['where'];
        // 拼接group
        !empty($this->options['group']) && $sql = $sql . 'GROUP BY ' . $this->options['group'] . ' ';
        // 拼接having
        !empty($this->options['having']) && $sql = $sql . 'HAVING ' . $this->options['having'] . ' ';
        // 拼接order
        !empty($this->options['order']) && $sql = $sql . 'ORDER BY ' . $this->options['order'] . ' ';
        // 拼接limit
        !empty($this->options['limit']) && $sql = $sql . 'LIMIT ' . implode(',' , $this->options['limit']) . ' ';

        // 返回sql语句
        return rtrim($sql);
    }

    /**
     * 重置sql语句
     */
    protected function resetSql(){
        $options['table'] = $this->options['table'];
        $this->bind = [];
        $this->options = $options;
        $this->fetchSql = false;
    }

    /**
     * 添加一条数据
     * @access public
     * @param array     $data 新增的数据
     * @return string
     */
    public function insert(array $data){
        $keys = [];
        $values = [];
        foreach($data as $k => $v){
            $keys[] = '`'.$k.'`';
            $values[] = ':' . $k;
        }
        // 生成sql语句
        $sql = 'INSERT INTO `' . $this->options['table'] . '` (' . implode(',',$keys) . ') VALUES (' .implode(',',$values) . ')';
        // 返回真实sql语句
        if($this->fetchSql){
            $realSql = $this->connection->getRealSql($sql , $data);
            // 重置sql信息
            $this->resetSql();
            return $realSql;
        }
        // 执行添加数据
        $result = $this->connection->execute($sql, $data);

        // 重置sql信息
        $this->resetSql();

        if($result > 0){
            return $this->connection->getLastInsID();
        }else{
            return false;
        }
    }

    /**
     * 添加多条数据
     * @access public
     * @param array     $data 新增的数据
     * @return string
     */
    public function insertAll(array $data){
        $keys = '';
        $values = [];
        foreach($data as $k => $v){
            empty($keys) && $keys = '`' . implode('`,`' , array_keys($v)) . '`';
            $values[] = '(' . implode(',',array_map([$this , 'parseValue'] , $v)) . ')';
        }
        // 生成实际执行的sql语句
        $sql = 'INSERT INTO `' . $this->options['table'] . '` (' . $keys . ') VALUES ' . implode(',',$values);
        // 返回真实sql语句
        if($this->fetchSql){
            $realSql = $this->connection->getRealSql($sql , []);
            // 重置sql信息
            $this->resetSql();
            return $realSql;
        }
        // 执行添加数据
        $result = $this->connection->execute($sql , []);

        // 重置sql信息
        $this->resetSql();

        return $result;
    }

    // 数据集处理
    private function parseValue($val){
        return !is_numeric($val) ? "'" . $val . "'" : $val;
    }

    /**
     * 更新一条记录
     * @access public
     * @param mixed $data 数据
     * @return integer|string
     * @throws Exception
     * @throws PDOException
     */
    public function update(array $data = []){
        if(empty($this->options['where'])){
            // where主键不存在，自动以第一个键值作为主键更新
            $where = key($data) . " = " . ':' . key($data);
            $bind = $data;    // 参数绑定
            array_shift($data);
        }else{
            $i = 1;
            $keys = [];
            foreach($data as $k => $v){
                if(isset($this->bind[$k])){
                    $kn = $k . $i;
                    $i++;
                    $this->bind[$kn] = $v;
                    $keys[] = $this->parseUpdateKeys($k , $kn);
                }else{
                    $this->bind[$k] = $v;
                    $keys[] = $this->parseUpdateKeys($k);
                }
            }
            $bind = array_merge($this->bind , $data);
            $where = $this->options['where'];
        }

        // 生成sql语句
        $sql = "UPDATE `" . $this->options['table'] . '` SET ' . implode(',',$keys) . ' WHERE ' . $where;

        // 返回真实sql语句
        if($this->fetchSql){
            $realSql = $this->connection->getRealSql($sql , $this->bind);
            // 重置sql信息
            $this->resetSql();
            return $realSql;
        }

        // 执行数据更新
        $result = $this->connection->execute($sql , $bind);

        // 重置sql信息
        $this->resetSql();

        return true;
    }

    // 更新记录键名处理
    private function parseUpdateKeys($k , $kn = ''){
        empty($kn) && $kn = $k;
        return "`". $k ."` = " . ":" . $kn;
    }

    /**
     * 字段值增长
     * @access public
     * @param string  $field    字段名
     * @param integer $step     增长值
     * @return integer|true
     * @throws Exception
     */
    public function setInc($field, $step = null){
        if(empty($this->options['where'])){
            throw new \Exception("缺少where条件！");
        }
        $step = is_null($step) ? 1 : $step;
        if(!is_numeric($step)){
            throw new \Exception("自增或自减必须为数字！");
        }
        $where = $this->options['where'];
        // 生成sql语句
        $sql = "UPDATE `" . $this->options['table'] . '` SET ' . $field . '=' . $field . ' + ' . $step . ' WHERE ' . $where;
        // 返回真实sql语句
        if($this->fetchSql){
            $realSql = $this->connection->getRealSql($sql , $this->bind);
            // 重置sql信息
            $this->resetSql();
            return $realSql;
        }
        // 执行数据更新
        $result = $this->connection->execute($sql , $this->bind);

        // 重置sql信息
        $this->resetSql();

        return $result;
    }

    /**
     * 字段值减少
     * @access public
     * @param string  $field    字段名
     * @param integer $step     增长值
     * @return integer|true
     * @throws Exception
     */
    public function setDec($field, $step = null){
        if(empty($this->options['where'])){
            throw new \Exception("缺少where条件！");
        }
        $step = is_null($step) ? 1 : $step;
        if(!is_numeric($step)){
            throw new \Exception("自增或自减必须为数字！");
        }
        $where = $this->options['where'];
        // 生成sql语句
        $sql = "UPDATE `" . $this->options['table'] . '` SET ' . $field . '=' . $field . ' - ' . $step . ' WHERE ' . $where;
        // 返回真实sql语句
        if($this->fetchSql){
            $realSql = $this->connection->getRealSql($sql , $this->bind);
            // 重置sql信息
            $this->resetSql();
            return $realSql;
        }
        // 执行数据更新
        $result = $this->connection->execute($sql , $this->bind);

        // 重置sql信息
        $this->resetSql();

        return $result;
    }

    /**
     * 设置记录的某个字段值
     * 支持使用数据库字段和方法
     * @access public
     * @param string|array $field 字段名
     * @param mixed        $value 字段值
     * @return integer
     */
    public function setField($field, $value = '')
    {
        if(empty($this->options['where'])){
            throw new \Exception("缺少where条件！");
        }
        if (is_array($field)) {
            $data = $field;
        } else {
            $data[$field] = $value;
        }
        return $this->update($data);
    }

    /**
     * 删除记录
     * @access public
     * @param mixed $data 表达式 true 表示强制删除
     * @return int
     * @throws Exception
     * @throws PDOException
     */
    public function delete(){
        if(empty($this->options['where'])){
            throw new \Exception("缺少where条件！");
        }
        // 生成sql语句
        $sql = 'DELETE FROM ' . $this->options['table'] . ' WHERE ' . $this->options['where'];

        // 返回真实sql语句
        if($this->fetchSql){
            $realSql = $this->connection->getRealSql($sql , $this->bind);
            // 重置sql信息
            $this->resetSql();
            return $realSql;
        }
        // 执行数据更新
        $result = $this->connection->execute($sql , $this->bind);

        // 重置sql信息
        $this->resetSql();

        return true;
    }

    /**
     * 返回sql语句
     * @access public
     * @return $this
     */
    public function fetchSql(){
        $this->fetchSql = true;
        return $this;
    }

    /**
     * 指定group查询
     * @access public
     * @param string $group GROUP
     * @return $this
     */
    public function group($group)
    {
        $this->options['group'] = $group;
        return $this;
    }

    /**
     * 指定having查询
     * @access public
     * @param string $having having
     * @return $this
     */
    public function having($having)
    {
        $this->options['having'] = $having;
        return $this;
    }

    /**
     * 指定分页
     * @access public
     * @param mixed $page     页数
     * @param mixed $listRows 每页数量
     * @return $this
     */
    public function page($page = null, $listRows = null){
        if(!is_null($page) && !is_null($listRows)){
            $offset = ($page - 1) * $listRows;
            $length = $listRows;
            return $this->limit($offset , $length);
        }
    }

    /**
     * 指定查询数量
     * @access public
     * @param mixed $offset 起始位置
     * @param mixed $length 查询数量
     * @return $this
     */
    public function limit($offset = null, $length = null)
    {
        if(!is_null($offset)){
            $limit = [];
            $limit[] = is_null($length) ? 0 : $offset;
            $limit[] = is_null($length) ? $offset : $length;
            $this->options['limit'] = $limit;
        }
        return $this;
    }

    /**
     * 指定排序
     * @access public
     * @param string|array $order 排序表达式
     * @return $this
     */
    public function order($order = null){
        if(!is_null($order)){
            $this->options['order'] = $order;
        }
        return $this;
    }

    /**
     * 查询SQL组装 join
     * @access public
     * @param mixed  $table      关联的表名
     * @param mixed  $condition 条件
     * @param string $type      JOIN类型
     * @param mixed $prefix    自动添加表前缀
     * @return $this
     */
    public function join($table, $condition = null , $type = 'INNER' , $prefix = true){
        $table = strpos($table , $this->prefix) === false ? $this->prefix.$table : $table;
        $alias = isset($this->options['alias'][$table]) ? 'AS ' . $this->options['alias'][$table] . ' ' : '';
        $condition = is_null($condition) ? '' : 'ON ' . $condition;
        $join = strtoupper($type) . ' JOIN ' . $table . ' ' . $alias . $condition . ' ';
        $this->options['join'] .= $join;
        return $this;
    }

    /**
     * 指定数据表别名
     * @access public
     * @param mixed $alias   数据表别名
     * @param mixed $prefix   自动添加表前缀
     * @return $this
     */
    public function alias($alias , $value = ''){
        if(is_array($alias) && !empty($alias)){
            foreach($alias as $key => $val){
                strpos($key , $this->prefix) === false && $key = $this->prefix . $key;
                $this->options['alias'][$key] = $val;
            }
        }elseif(is_string($alias) && !empty($value)){
            strpos($alias , $this->prefix) === false && $alias = $this->prefix . $alias;
            $this->options['alias'][$alias] = $value;
        }
        return $this;
    }

    /**
     * 参数绑定
     * @access public
     * @param mixed   $array   绑定参数
     * @return $this
     */
    public function bind($bind = []){
        if(!empty($bind)){
            $this->bind = array_merge($this->bind , $bind);
        }
        return $this;
    }

    /**
     * 指定查询字段
     * @access public
     * @param array|string   $field   查询字段
     * @param bool           $flag    是否设置不查询字段
     * @return $this
     */
    public function field($field = '' , $flag = false){
        if(!empty($field)){
            if(is_string($field)){
                $field = false === strpos($field , ',') ? (array)$field : explode(',' , $field);
            }
            if($flag === false){
                $str = implode(',' , $field);
                $this->options['field'] = empty($this->options['field']) ? $str : $this->options['field'] . ',' . $str;
            }else{
                $allFields = $this->getFields();
                $str = implode(',' , array_diff($allFields , $field));
                $this->options['field'] = empty($this->options['field']) ? $str : $this->options['field'] . ',' . $str;
            }
        }
        return $this;
    }
}